package org.lanqiao.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;

import org.lanqiao.pojo.CategoryPOJO;
import org.lanqiao.pojo.PageModel;
import org.lanqiao.pojo.ProductPOJO;
import org.lanqiao.util.SqlUtil;
import org.lanqiao.util.StringUtil;

public class ProductDaoImpl {
	/**
	 * 操作商品
	 */
	
	/**
	 * 全部商品搜索，默认排序 返回值商品所有信息及销量，并分页
	 */
	public PageModel<List> productSelectDefaultDao(String page,String pagesize){
		
		String sql="select sum(e.PRODUCT_SALE_COUNT)salenum ,e.PRODUCT_ID,e.PRODUCT_CODE,e.PRODUCT_NAME,e.PRODUCT_COLOR,e.PRODUCT_STANDARD,e.PRODUCT_UNIT,e.PRODUCT_PRICE,e.PRODUCT_CATEGORY_ID,e.PRODUCT_BRAND_ID,e.PRODUCT_PHOTO,e.PRODUCT_CAR_ID,e.PRODUCT_CLICK_COUNT,e.PRODUCT_SALE_COUNT,e.PRODUCT_IS_SALE,e.PRODUCT_STATUS,e.PRODUCT_ORDER,e.PRODUCT_SUGGEST_PRICE,e.PRODUCT_COMMENT,e.PRODUCT_DATE_TIME from(select *from product order by PRODUCT_SALE_COUNT desc)e  where PRODUCT_STATUS=1 group by e.PRODUCT_CODE,e.PRODUCT_ID,e.PRODUCT_CODE,e.PRODUCT_NAME,e.PRODUCT_COLOR,e.PRODUCT_STANDARD,e.PRODUCT_UNIT,e.PRODUCT_PRICE,e.PRODUCT_CATEGORY_ID,e.PRODUCT_BRAND_ID,e.PRODUCT_PHOTO,e.PRODUCT_CAR_ID,e.PRODUCT_CLICK_COUNT,e.PRODUCT_SALE_COUNT,e.PRODUCT_IS_SALE,e.PRODUCT_STATUS,e.PRODUCT_ORDER,e.PRODUCT_SUGGEST_PRICE,e.PRODUCT_COMMENT,e.PRODUCT_DATE_TIME ";
		//System.out.println(sql);
		PageModel pagemodel=new PageModel<>(sql,page,pagesize);
		List<LinkedHashMap<String, Object>> list=SqlUtil.select3(pagemodel.toMysqlSql());
		pagemodel.setList(list);
		List<LinkedHashMap<String, Object>> countList =SqlUtil.select3(pagemodel.toCountSql());
		String total=StringUtil.valueOf(countList.get(0).get("count"));
		pagemodel.setTotal(Integer.valueOf(total));
		return pagemodel;
	}
	/**
	 * 全部商品搜索，按销量排序 返回值商品所有信息及销量，并分页  传入参数 page第几页 pagesize页面大小
	 */
	public PageModel<List> productSaleCountDao(String page,String pagesize){
		
		String sql="select sum(e.PRODUCT_SALE_COUNT)salenum ,e.PRODUCT_ID,e.PRODUCT_CODE,e.PRODUCT_NAME,e.PRODUCT_COLOR,e.PRODUCT_STANDARD,e.PRODUCT_UNIT,e.PRODUCT_PRICE,e.PRODUCT_CATEGORY_ID,e.PRODUCT_BRAND_ID,e.PRODUCT_PHOTO,e.PRODUCT_CAR_ID,e.PRODUCT_CLICK_COUNT,e.PRODUCT_SALE_COUNT,e.PRODUCT_IS_SALE,e.PRODUCT_STATUS,e.PRODUCT_ORDER,e.PRODUCT_SUGGEST_PRICE,e.PRODUCT_COMMENT,e.PRODUCT_DATE_TIME from(select *from product order by PRODUCT_SALE_COUNT desc)e  where PRODUCT_STATUS=1 group by e.PRODUCT_CODE,e.PRODUCT_ID,e.PRODUCT_CODE,e.PRODUCT_NAME,e.PRODUCT_COLOR,e.PRODUCT_STANDARD,e.PRODUCT_UNIT,e.PRODUCT_PRICE,e.PRODUCT_CATEGORY_ID,e.PRODUCT_BRAND_ID,e.PRODUCT_PHOTO,e.PRODUCT_CAR_ID,e.PRODUCT_CLICK_COUNT,e.PRODUCT_SALE_COUNT,e.PRODUCT_IS_SALE,e.PRODUCT_STATUS,e.PRODUCT_ORDER,e.PRODUCT_SUGGEST_PRICE,e.PRODUCT_COMMENT,e.PRODUCT_DATE_TIME order by sum(e.PRODUCT_SALE_COUNT) desc";
		//System.out.println(sql);
		PageModel pagemodel=new PageModel<>(sql,page,pagesize);
		List<LinkedHashMap<String, Object>> list=SqlUtil.select3(pagemodel.toMysqlSql());
		pagemodel.setList(list);
		List<LinkedHashMap<String, Object>> countList =SqlUtil.select3(pagemodel.toCountSql());
		String total=StringUtil.valueOf(countList.get(0).get("count"));
		pagemodel.setTotal(Integer.valueOf(total));
		return pagemodel;
	}
	/**
	 * 全部商品搜索，按价格降序排序 返回值商品所有信息及销量，并分页  传入参数 page第几页 pagesize页面大小
	 */
	public PageModel<List> productPriceDao(String page,String pagesize){
		
		String sql="select sum(e.PRODUCT_SALE_COUNT)salenum ,e.PRODUCT_ID,e.PRODUCT_CODE,e.PRODUCT_NAME,e.PRODUCT_COLOR,e.PRODUCT_STANDARD,e.PRODUCT_UNIT,e.PRODUCT_PRICE,e.PRODUCT_CATEGORY_ID,e.PRODUCT_BRAND_ID,e.PRODUCT_PHOTO,e.PRODUCT_CAR_ID,e.PRODUCT_CLICK_COUNT,e.PRODUCT_SALE_COUNT,e.PRODUCT_IS_SALE,e.PRODUCT_STATUS,e.PRODUCT_ORDER,e.PRODUCT_SUGGEST_PRICE,e.PRODUCT_COMMENT,e.PRODUCT_DATE_TIME from(select *from product order by PRODUCT_SALE_COUNT desc)e  where PRODUCT_STATUS=1 group by e.PRODUCT_CODE,e.PRODUCT_ID,e.PRODUCT_CODE,e.PRODUCT_NAME,e.PRODUCT_COLOR,e.PRODUCT_STANDARD,e.PRODUCT_UNIT,e.PRODUCT_PRICE,e.PRODUCT_CATEGORY_ID,e.PRODUCT_BRAND_ID,e.PRODUCT_PHOTO,e.PRODUCT_CAR_ID,e.PRODUCT_CLICK_COUNT,e.PRODUCT_SALE_COUNT,e.PRODUCT_IS_SALE,e.PRODUCT_STATUS,e.PRODUCT_ORDER,e.PRODUCT_SUGGEST_PRICE,e.PRODUCT_COMMENT,e.PRODUCT_DATE_TIME order by e.PRODUCT_PRICE asc";
		//System.out.println(sql);
		PageModel pagemodel=new PageModel<>(sql,page,pagesize);
		List<LinkedHashMap<String, Object>> list=SqlUtil.select3(pagemodel.toMysqlSql());
		pagemodel.setList(list);
		List<LinkedHashMap<String, Object>> countList =SqlUtil.select3(pagemodel.toCountSql());
		String total=StringUtil.valueOf(countList.get(0).get("count"));
		pagemodel.setTotal(Integer.valueOf(total));
		return pagemodel;
	}
	/**
	 * 查询商品（单个）
	 */
	public List<LinkedHashMap<String, Object>> productSelectOneDao(ProductPOJO product){
		List<LinkedHashMap<String,Object>> list=null;
		String sql="select *from product where PRODUCT_ID=? ";
		list=SqlUtil.select3(sql,product.getProductId());
		return list;
	}
	/**
	 * 下架商品，传product类，通过Id查询状态置零，再查询一遍返回刚才下架的商品信息
	 */
	public List<LinkedHashMap<String, Object>> cancelPuductDao(ProductPOJO product){
		int row=0;
		List<LinkedHashMap<String,Object>> list=null;
		String sql="update product set PRODUCT_STATUS=0 where PRODUCT_ID=?";
		row=SqlUtil.update(sql,product.getProductId());
		String sql1="select *from product where PRODUCT_ID=? and PRODUCT_STATUS=1";
		list=SqlUtil.select3(sql1,product.getProductId());
		return list;
	}
	/**
	 * 上新商品 传入product的类 返回整数
	 */
	public int addPuductDao(ProductPOJO product){
		int row=0;
		
		String sql="insert into product values (1,?,?,?,?,?,?,?,?,?,null,1,1,1,0,1,?,?,?,NOW()) ";
		row=SqlUtil.update(sql,product.getProductCode(),product.getProductName(),product.getProductColor(),product.getStandard(),product.getUnit(),product.getPrice(),product.getCategoryId(),product.getBrandId(),product.getPhoto(),product.getOrder(),product.getSuggestPrice(),product.getProductComment());
//		String sql1="select *from product where PRODUCT_ID=?";
//		list=SqlUtil.select3(sql1,product.getProductId());
		return row;
	}

	/**
	 * 修改商品信息
	 */
	public int changeProductDao(ProductPOJO product){
		int row=0;
		List<LinkedHashMap<String,Object>> list=null;
		String sql="update product set PRODUCT_NAME=?,PRODUCT_COLOR=?,PRODUCT_PRICE=?,PRODUCT_CATEGORY_ID=?,PRODUCT_IS_SALE=?,PRODUCT_IS_LACK=?,PRODUCT_STATUS=? where PRODUCT_ID=? ";
		row=SqlUtil.update(sql,product.getProductName(),product.getProductColor(),product.getPrice(),product.getCategoryId(),product.getSale(),product.getLack(),product.getStatus(),product.getProductId());
		return row;
	}
	
	/**
	 * 搜索商品分页
	 */
	public PageModel<List> productRowSelectDao(String page,String pageSize){
		
		String sql="select e.PRODUCT_ID,e.PRODUCT_CODE,e.PRODUCT_NAME,e.PRODUCT_COLOR,e.PRODUCT_STANDARD,e.PRODUCT_UNIT,e.PRODUCT_PRICE,e.PRODUCT_CATEGORY_ID,e.PRODUCT_BRAND_ID,e.PRODUCT_PHOTO,e.PRODUCT_CAR_ID,e.PRODUCT_CLICK_COUNT,e.PRODUCT_SALE_COUNT,e.PRODUCT_IS_SALE,e.PRODUCT_STATUS,e.PRODUCT_ORDER,e.PRODUCT_SUGGEST_PRICE,e.PRODUCT_COMMENT,e.PRODUCT_DATE_TIME from product e where PRODUCT_STATUS=1 group by e.PRODUCT_CODE,e.PRODUCT_ID,e.PRODUCT_CODE,e.PRODUCT_NAME,e.PRODUCT_COLOR,e.PRODUCT_STANDARD,e.PRODUCT_UNIT,e.PRODUCT_PRICE,e.PRODUCT_CATEGORY_ID,e.PRODUCT_BRAND_ID,e.PRODUCT_PHOTO,e.PRODUCT_CAR_ID,e.PRODUCT_CLICK_COUNT,e.PRODUCT_SALE_COUNT,e.PRODUCT_IS_SALE,e.PRODUCT_STATUS,e.PRODUCT_ORDER,e.PRODUCT_SUGGEST_PRICE,e.PRODUCT_COMMENT,e.PRODUCT_DATE_TIME order by PRODUCT_SALE_COUNT desc ";
		PageModel pagemodel=new PageModel<>(sql,page,pageSize);
		List<LinkedHashMap<String, Object>> list=SqlUtil.select3(pagemodel.toMysqlSql());
		pagemodel.setList(list);
		List<LinkedHashMap<String, Object>> countList =SqlUtil.select3(pagemodel.toCountSql());
		String total=StringUtil.valueOf(countList.get(0).get("count"));
		pagemodel.setTotal(Integer.valueOf(total));
		
		return pagemodel;
	}
	/**
	 * 通过productCode进入详情页 查出固定商品名与描述
	 */
	public List<LinkedHashMap<String, Object>> descriptionDao(ProductPOJO product){
		List<LinkedHashMap<String,Object>> list=null;
		String sql="SELECT PRODUCT_COMMENT ,PRODUCT_NAME,PRODUCT_PHOTO  from product  WHERE  PRODUCT_CODE=? GROUP BY PRODUCT_COMMENT,PRODUCT_NAME,PRODUCT_PHOTO ";
		list=SqlUtil.select3(sql,product.getProductCode());
		return list;
	}
	/**
	 * 详情页通过productCode查出规格
	 */
	public List<LinkedHashMap<String, Object>> standardDao(ProductPOJO product){
		List<LinkedHashMap<String,Object>> list=null;
		String sql="SELECT PRODUCT_STANDARD from product  WHERE  PRODUCT_CODE=? GROUP BY  PRODUCT_STANDARD ";
		list=SqlUtil.select3(sql,product.getProductCode());
		return list;
	}
	/**
	 * 详情页通过productCode查出规格，价钱
	 */
	public List<LinkedHashMap<String, Object>> standPriceDao(ProductPOJO product){
		List<LinkedHashMap<String,Object>> list=null;
		String sql="SELECT PRODUCT_STANDARD ,PRODUCT_PRICE from product  WHERE  PRODUCT_CODE=? GROUP BY PRODUCT_STANDARD,PRODUCT_PRICE";
		list=SqlUtil.select3(sql,product.getProductCode());
		return list;
	}
	/**
	 * 详情页通过productCode查出颜色
	 */
	public List<LinkedHashMap<String, Object>> colorDao(ProductPOJO product){
		List<LinkedHashMap<String,Object>> list=null;
		String sql="SELECT PRODUCT_COLOR from product  WHERE  PRODUCT_CODE=? GROUP BY  PRODUCT_COLOR";
		list=SqlUtil.select3(sql,product.getProductCode());
		return list;
	}
	/**
	 * 通过价钱和颜色,商品名查出商品id
	 */
	public List<LinkedHashMap<String, Object>> productIdSelectDao(ProductPOJO product){
		List<LinkedHashMap<String,Object>> list=null;
		String sql="SELECT PRODUCT_ID from product  WHERE  PRODUCT_COLOR=? and PRODUCT_PRICE=? and PRODUCT_NAME=?";
		list=SqlUtil.select3(sql,product.getProductColor(),product.getPrice(),product.getProductName());
		return list;
	}
	/**
	 * 同类型销量排序
	 */
//	public List<LinkedHashMap<String, Object>> SaleNumDao(CategoryPOJO cate){
//		List<LinkedHashMap<String,Object>> list=null;
//		String sql="select sum(e.PRODUCT_SALE_COUNT)salenum,e.* from(SELECT p.* from product p left join category c on p.PRODUCT_CATEGORY_ID=c.CATEGORY_ID where p.PRODUCT_STATUS=1 and c.CATEGORY_NAME=?)e group by PRODUCT_CODE order by sum(e.PRODUCT_SALE_COUNT) desc";
//		list=SqlUtil.select3(sql,cate.getName());
//		return list;
//	}
	/**
	 * 同类型按默认排序，且分页 传入参数catename类名，page页数，pagesize页面大小
	 */
	//通过类名查商品  返回商品和对应的类型
	public PageModel<List> selectCategoryDao(CategoryPOJO cate,String page,String pageSize){
		
		String sql="select sum(e.PRODUCT_SALE_COUNT)salenum,e.PRODUCT_ID,e.PRODUCT_CODE,e.PRODUCT_NAME,e.PRODUCT_COLOR,e.PRODUCT_STANDARD,e.PRODUCT_UNIT,e.PRODUCT_PRICE,e.PRODUCT_CATEGORY_ID,e.PRODUCT_BRAND_ID,e.PRODUCT_PHOTO,e.PRODUCT_CAR_ID,e.PRODUCT_CLICK_COUNT,e.PRODUCT_SALE_COUNT,e.PRODUCT_IS_SALE,e.PRODUCT_STATUS,e.PRODUCT_ORDER,e.PRODUCT_SUGGEST_PRICE,e.PRODUCT_COMMENT,e.PRODUCT_DATE_TIME from(SELECT p.* from product p left join category c on p.PRODUCT_CATEGORY_ID=c.CATEGORY_ID where p.PRODUCT_STATUS=1 and c.CATEGORY_NAME="+"'"+cate.getName()+"'"+")e group by e.PRODUCT_CODE,e.PRODUCT_ID,e.PRODUCT_CODE,e.PRODUCT_NAME,e.PRODUCT_COLOR,e.PRODUCT_STANDARD,e.PRODUCT_UNIT,e.PRODUCT_PRICE,e.PRODUCT_CATEGORY_ID,e.PRODUCT_BRAND_ID,e.PRODUCT_PHOTO,e.PRODUCT_CAR_ID,e.PRODUCT_CLICK_COUNT,e.PRODUCT_SALE_COUNT,e.PRODUCT_IS_SALE,e.PRODUCT_STATUS,e.PRODUCT_ORDER,e.PRODUCT_SUGGEST_PRICE,e.PRODUCT_COMMENT,e.PRODUCT_DATE_TIME";
		//System.out.println(sql);
		PageModel pagemodel=new PageModel<>(sql,page,pageSize);
		List<LinkedHashMap<String, Object>> list=SqlUtil.select3(pagemodel.toMysqlSql());
		pagemodel.setList(list);
		List<LinkedHashMap<String, Object>> countList =SqlUtil.select3(pagemodel.toCountSql());
		String total=StringUtil.valueOf(countList.get(0).get("count"));
		pagemodel.setTotal(Integer.valueOf(total));
		
		return pagemodel;
	}
	/**
	 * 同类型按销量排序，且分页 传入参数catename类名，page页数，pagesize页面大小
	 * @param cate
	 * @param page
	 * @param pageSize
	 * @return
	 */
	public PageModel<List> SaleNumDao(CategoryPOJO cate,String page,String pageSize){
		
		String sql="select sum(e.PRODUCT_SALE_COUNT)salenum,e.PRODUCT_ID,e.PRODUCT_CODE,e.PRODUCT_NAME,e.PRODUCT_COLOR,e.PRODUCT_STANDARD,e.PRODUCT_UNIT,e.PRODUCT_PRICE,e.PRODUCT_CATEGORY_ID,e.PRODUCT_BRAND_ID,e.PRODUCT_PHOTO,e.PRODUCT_CAR_ID,e.PRODUCT_CLICK_COUNT,e.PRODUCT_SALE_COUNT,e.PRODUCT_IS_SALE,e.PRODUCT_STATUS,e.PRODUCT_ORDER,e.PRODUCT_SUGGEST_PRICE,e.PRODUCT_COMMENT,e.PRODUCT_DATE_TIME from(SELECT p.* from product p left join category c on p.PRODUCT_CATEGORY_ID=c.CATEGORY_ID where p.PRODUCT_STATUS=1 and c.CATEGORY_NAME="+"'"+cate.getName()+"'"+")e group by e.PRODUCT_CODE,e.PRODUCT_ID,e.PRODUCT_CODE,e.PRODUCT_NAME,e.PRODUCT_COLOR,e.PRODUCT_STANDARD,e.PRODUCT_UNIT,e.PRODUCT_PRICE,e.PRODUCT_CATEGORY_ID,e.PRODUCT_BRAND_ID,e.PRODUCT_PHOTO,e.PRODUCT_CAR_ID,e.PRODUCT_CLICK_COUNT,e.PRODUCT_SALE_COUNT,e.PRODUCT_IS_SALE,e.PRODUCT_STATUS,e.PRODUCT_ORDER,e.PRODUCT_SUGGEST_PRICE,e.PRODUCT_COMMENT,e.PRODUCT_DATE_TIME order by sum(e.PRODUCT_SALE_COUNT) desc";
	//	System.out.println(sql);
		PageModel pagemodel=new PageModel<>(sql,page,pageSize);
		List<LinkedHashMap<String, Object>> list=SqlUtil.select3(pagemodel.toMysqlSql());
		pagemodel.setList(list);
		List<LinkedHashMap<String, Object>> countList =SqlUtil.select3(pagemodel.toCountSql());
		String total=StringUtil.valueOf(countList.get(0).get("count"));
		pagemodel.setTotal(Integer.valueOf(total));
		
		return pagemodel;
	}
	/**
	 * 同类型按价格排序，且分页 传入参数catename类名，page页数，pagesize页面大小
	 * @param cate
	 * @param page
	 * @param pageSize
	 * @return
	 */
	public PageModel<List> priceNumDao(CategoryPOJO cate,String page,String pageSize){
		
		String sql="select sum(e.PRODUCT_SALE_COUNT)salenum,e.PRODUCT_ID,e.PRODUCT_CODE,e.PRODUCT_NAME,e.PRODUCT_COLOR,e.PRODUCT_STANDARD,e.PRODUCT_UNIT,e.PRODUCT_PRICE,e.PRODUCT_CATEGORY_ID,e.PRODUCT_BRAND_ID,e.PRODUCT_PHOTO,e.PRODUCT_CAR_ID,e.PRODUCT_CLICK_COUNT,e.PRODUCT_SALE_COUNT,e.PRODUCT_IS_SALE,e.PRODUCT_STATUS,e.PRODUCT_ORDER,e.PRODUCT_SUGGEST_PRICE,e.PRODUCT_COMMENT,e.PRODUCT_DATE_TIME from(SELECT p.* from product p left join category c on p.PRODUCT_CATEGORY_ID=c.CATEGORY_ID where p.PRODUCT_STATUS=1 and c.CATEGORY_NAME="+"'"+cate.getName()+"'"+")e group by e.PRODUCT_CODE,e.PRODUCT_ID,e.PRODUCT_CODE,e.PRODUCT_NAME,e.PRODUCT_COLOR,e.PRODUCT_STANDARD,e.PRODUCT_UNIT,e.PRODUCT_PRICE,e.PRODUCT_CATEGORY_ID,e.PRODUCT_BRAND_ID,e.PRODUCT_PHOTO,e.PRODUCT_CAR_ID,e.PRODUCT_CLICK_COUNT,e.PRODUCT_SALE_COUNT,e.PRODUCT_IS_SALE,e.PRODUCT_STATUS,e.PRODUCT_ORDER,e.PRODUCT_SUGGEST_PRICE,e.PRODUCT_COMMENT,e.PRODUCT_DATE_TIME order by e.PRODUCT_PRICE asc";
		//System.out.println(sql);
		PageModel pagemodel=new PageModel<>(sql,page,pageSize);
		List<LinkedHashMap<String, Object>> list=SqlUtil.select3(pagemodel.toMysqlSql());
		pagemodel.setList(list);
		List<LinkedHashMap<String, Object>> countList =SqlUtil.select3(pagemodel.toCountSql());
		String total=StringUtil.valueOf(countList.get(0).get("count"));
		pagemodel.setTotal(Integer.valueOf(total));
		
		return pagemodel;
	}
	/**
	 * 模糊搜索商品按默认排序，前台传入商品模糊名productname，page页数，pagesize页面大小，返回商品总条数和商品所有信息
	 * 
	 */
	public PageModel<List> fuzzyDefaultDao(ProductPOJO product,String page,String pageSize){
		String str=product.getProductName();
		String productname=str.replaceAll(" ","");
		
		//String sql="select p.*,c.* from product p left join category c on CATEGORY_ID=PRODUCT_CATEGORY_ID  where p.PRODUCT_NAME like "+ "'%"+name+"%'"+" GROUP BY p.PRODUCT_CODE";
		String sql="select sum(e.PRODUCT_SALE_COUNT)salenum,e.PRODUCT_ID,e.PRODUCT_CODE,e.PRODUCT_NAME,e.PRODUCT_COLOR,e.PRODUCT_STANDARD,e.PRODUCT_UNIT,e.PRODUCT_PRICE,e.PRODUCT_CATEGORY_ID,e.PRODUCT_BRAND_ID,e.PRODUCT_PHOTO,e.PRODUCT_CAR_ID,e.PRODUCT_CLICK_COUNT,e.PRODUCT_SALE_COUNT,e.PRODUCT_IS_SALE,e.PRODUCT_STATUS,e.PRODUCT_ORDER,e.PRODUCT_SUGGEST_PRICE,e.PRODUCT_COMMENT,e.PRODUCT_DATE_TIME from (select *from product where PRODUCT_NAME like"  + "'%"+productname+"%'"+  "and PRODUCT_STATUS=1)e GROUP  BY e.PRODUCT_CODE,e.PRODUCT_ID,e.PRODUCT_CODE,e.PRODUCT_NAME,e.PRODUCT_COLOR,e.PRODUCT_STANDARD,e.PRODUCT_UNIT,e.PRODUCT_PRICE,e.PRODUCT_CATEGORY_ID,e.PRODUCT_BRAND_ID,e.PRODUCT_PHOTO,e.PRODUCT_CAR_ID,e.PRODUCT_CLICK_COUNT,e.PRODUCT_SALE_COUNT,e.PRODUCT_IS_SALE,e.PRODUCT_STATUS,e.PRODUCT_ORDER,e.PRODUCT_SUGGEST_PRICE,e.PRODUCT_COMMENT,e.PRODUCT_DATE_TIME";
		PageModel pagemodel=new PageModel<>(sql,page,pageSize);
		List<LinkedHashMap<String, Object>> list=SqlUtil.select3(pagemodel.toMysqlSql());
		pagemodel.setList(list);
		List<LinkedHashMap<String, Object>> countList =SqlUtil.select3(pagemodel.toCountSql());
		String total=StringUtil.valueOf(countList.get(0).get("count"));
		pagemodel.setTotal(Integer.valueOf(total));
	//	System.out.println(sql);
		return pagemodel;
		
	}
	/**
	 * 模糊搜索商品按销量排序，前台传入商品模糊名productname，page页数，pagesize页面大小，返回商品总条数和商品所有信息
	 * 
	 */
	public PageModel<List> fuzzySaleCountDao(ProductPOJO product,String page,String pageSize){
		String str=product.getProductName();
		String productname=str.replaceAll(" ","");
		
		//String sql="select p.*,c.* from product p left join category c on CATEGORY_ID=PRODUCT_CATEGORY_ID  where p.PRODUCT_NAME like "+ "'%"+name+"%'"+" GROUP BY p.PRODUCT_CODE";
		String sql="select sum(e.PRODUCT_SALE_COUNT)salenum,e.PRODUCT_ID,e.PRODUCT_CODE,e.PRODUCT_NAME,e.PRODUCT_COLOR,e.PRODUCT_STANDARD,e.PRODUCT_UNIT,e.PRODUCT_PRICE,e.PRODUCT_CATEGORY_ID,e.PRODUCT_BRAND_ID,e.PRODUCT_PHOTO,e.PRODUCT_CAR_ID,e.PRODUCT_CLICK_COUNT,e.PRODUCT_SALE_COUNT,e.PRODUCT_IS_SALE,e.PRODUCT_STATUS,e.PRODUCT_ORDER,e.PRODUCT_SUGGEST_PRICE,e.PRODUCT_COMMENT,e.PRODUCT_DATE_TIME from (select *from product where PRODUCT_NAME like"  + "'%"+productname+"%'"+  "and PRODUCT_STATUS=1)e GROUP  BY e.PRODUCT_CODE,e.PRODUCT_ID,e.PRODUCT_CODE,e.PRODUCT_NAME,e.PRODUCT_COLOR,e.PRODUCT_STANDARD,e.PRODUCT_UNIT,e.PRODUCT_PRICE,e.PRODUCT_CATEGORY_ID,e.PRODUCT_BRAND_ID,e.PRODUCT_PHOTO,e.PRODUCT_CAR_ID,e.PRODUCT_CLICK_COUNT,e.PRODUCT_SALE_COUNT,e.PRODUCT_IS_SALE,e.PRODUCT_STATUS,e.PRODUCT_ORDER,e.PRODUCT_SUGGEST_PRICE,e.PRODUCT_COMMENT,e.PRODUCT_DATE_TIME order by sum(e.PRODUCT_SALE_COUNT) desc";
		PageModel pagemodel=new PageModel<>(sql,page,pageSize);
		List<LinkedHashMap<String, Object>> list=SqlUtil.select3(pagemodel.toMysqlSql());
		pagemodel.setList(list);
		List<LinkedHashMap<String, Object>> countList =SqlUtil.select3(pagemodel.toCountSql());
		String total=StringUtil.valueOf(countList.get(0).get("count"));
		pagemodel.setTotal(Integer.valueOf(total));
		//System.out.println(sql);
		return pagemodel;
		
	}
	/**
	 * 模糊搜索商品按价格排序，前台传入商品模糊名productname，page页数，pagesize页面大小，返回商品总条数和商品所有信息
	 * 
	 */
	public PageModel<List> fuzzyPriceDao(ProductPOJO product,String page,String pageSize){
		String str=product.getProductName();
		String productname=str.replaceAll(" ","");
		
		//String sql="select p.*,c.* from product p left join category c on CATEGORY_ID=PRODUCT_CATEGORY_ID  where p.PRODUCT_NAME like "+ "'%"+name+"%'"+" GROUP BY p.PRODUCT_CODE";
		String sql="select sum(e.PRODUCT_SALE_COUNT)salenum,e.PRODUCT_ID,e.PRODUCT_CODE,e.PRODUCT_NAME,e.PRODUCT_COLOR,e.PRODUCT_STANDARD,e.PRODUCT_UNIT,e.PRODUCT_PRICE,e.PRODUCT_CATEGORY_ID,e.PRODUCT_BRAND_ID,e.PRODUCT_PHOTO,e.PRODUCT_CAR_ID,e.PRODUCT_CLICK_COUNT,e.PRODUCT_SALE_COUNT,e.PRODUCT_IS_SALE,e.PRODUCT_STATUS,e.PRODUCT_ORDER,e.PRODUCT_SUGGEST_PRICE,e.PRODUCT_COMMENT,e.PRODUCT_DATE_TIME from (select *from product where PRODUCT_NAME like " + "'%"+productname+"%'"+  " and PRODUCT_STATUS=1)e GROUP  BY e.PRODUCT_CODE,e.PRODUCT_ID,e.PRODUCT_CODE,e.PRODUCT_NAME,e.PRODUCT_COLOR,e.PRODUCT_STANDARD,e.PRODUCT_UNIT,e.PRODUCT_PRICE,e.PRODUCT_CATEGORY_ID,e.PRODUCT_BRAND_ID,e.PRODUCT_PHOTO,e.PRODUCT_CAR_ID,e.PRODUCT_CLICK_COUNT,e.PRODUCT_SALE_COUNT,e.PRODUCT_IS_SALE,e.PRODUCT_STATUS,e.PRODUCT_ORDER,e.PRODUCT_SUGGEST_PRICE,e.PRODUCT_COMMENT,e.PRODUCT_DATE_TIME order by e.PRODUCT_PRICE asc";
		PageModel pagemodel=new PageModel<>(sql,page,pageSize);
		List<LinkedHashMap<String, Object>> list=SqlUtil.select3(pagemodel.toMysqlSql());
		pagemodel.setList(list);
		List<LinkedHashMap<String, Object>> countList =SqlUtil.select3(pagemodel.toCountSql());
		String total=StringUtil.valueOf(countList.get(0).get("count"));
		pagemodel.setTotal(Integer.valueOf(total));
		//System.out.println(sql);
		return pagemodel;
		
	}
	
	/**
	 * 测试分页
	 * @param product
	 * @param page
	 * @param pageSize
	 * @return
	 */
	
	
	public PageModel<List> testFENyeDao(ProductPOJO product,String page,String pageSize){
		String str=product.getProductName();
		String productname=str.replaceAll(" ","");
		//String sql="select p.*,c.* from product p left join category c on CATEGORY_ID=PRODUCT_CATEGORY_ID  where p.PRODUCT_NAME like "+ "'%"+name+"%'"+" GROUP BY p.PRODUCT_CODE";
		String sql="select sum(e.PRODUCT_SALE_COUNT)salenum,e.PRODUCT_ID,e.PRODUCT_CODE,e.PRODUCT_NAME,e.PRODUCT_COLOR,e.PRODUCT_STANDARD,e.PRODUCT_UNIT,e.PRODUCT_PRICE,e.PRODUCT_CATEGORY_ID,e.PRODUCT_BRAND_ID,e.PRODUCT_PHOTO,e.PRODUCT_CAR_ID,e.PRODUCT_CLICK_COUNT,e.PRODUCT_SALE_COUNT,e.PRODUCT_IS_SALE,e.PRODUCT_STATUS,e.PRODUCT_ORDER,e.PRODUCT_SUGGEST_PRICE,e.PRODUCT_COMMENT,e.PRODUCT_DATE_TIME from (select *from product where PRODUCT_NAME like " + "'%"+productname+"%'"+  " and PRODUCT_STATUS=1)e GROUP  BY e.PRODUCT_CODE,e.PRODUCT_ID,e.PRODUCT_CODE,e.PRODUCT_NAME,e.PRODUCT_COLOR,e.PRODUCT_STANDARD,e.PRODUCT_UNIT,e.PRODUCT_PRICE,e.PRODUCT_CATEGORY_ID,e.PRODUCT_BRAND_ID,e.PRODUCT_PHOTO,e.PRODUCT_CAR_ID,e.PRODUCT_CLICK_COUNT,e.PRODUCT_SALE_COUNT,e.PRODUCT_IS_SALE,e.PRODUCT_STATUS,e.PRODUCT_ORDER,e.PRODUCT_SUGGEST_PRICE,e.PRODUCT_COMMENT,e.PRODUCT_DATE_TIME order by e.PRODUCT_PRICE asc";
		PageModel pagemodel=new PageModel<>(sql,page,pageSize);
		List<LinkedHashMap<String, Object>> list=SqlUtil.select3(pagemodel.toMysqlSql());
		pagemodel.setList(list);
		List<LinkedHashMap<String, Object>> countList =SqlUtil.select3(pagemodel.toCountSql());
		String total=StringUtil.valueOf(countList.get(0).get("count"));
		pagemodel.setTotal(Integer.valueOf(total));
		pagemodel.setTotalPage(pagemodel.getTotalPage());//获取总页数
		System.out.println(sql);
		return pagemodel;
		
	}
	
	
	
}
